﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace Hotel
{
    public partial class Checkout : Form
    {
        
        public Checkout()
        {
            InitializeComponent();
        }
        CNDatabase cnDb = new CNDatabase();
        private void txtIdentify_TextChanged(object sender, EventArgs e)
        {
            //txtFullName.Text = getCustomer("Fullname", "IdentificationNumber", txtIdentify.Text);
            lbName.Text = getCustomer("Fullname", "IdentificationNumber", txtIdentify.Text);
            txtPhone.Text = getCustomer("PhoneNumber", "IdentificationNumber", txtIdentify.Text);
            //txtAddress.Text = getCustomer("Address", "IdentificationNumber", txtIdentify.Text);
            lbAddress.Text = getCustomer("Address", "IdentificationNumber", txtIdentify.Text);
            //txtBirthday.Text = getCustomer("BirthDay", "IdentificationNumber", txtIdentify.Text);
            lbBirth.Text = getCustomer("BirthDay", "IdentificationNumber", txtIdentify.Text);
            try
            {
                string qr = "select COUNT(1) from BookingLog, ListRoomBooking "
                        + " where BookingLog.IDBookingLog=ListRoomBooking.IDBookingLog "
                        + " and IdentificationNumber='" + txtIdentify.Text + "'";
                lbArrived.Text = cnDb.getOneData(qr).ToString() + " times";

            }
            catch (Exception)
            {
                cnDb.closeConnect();
            }
        }
        private string getCustomer(string fieldSelect, string fieldCondition, string textbox) {
            try
            {
                string qr = "select top 1 "+fieldSelect+" from Customer where "+fieldCondition+" = '"+textbox+"'";
                return cnDb.getOneData(qr).ToString();
            }
            catch (Exception) {
                cnDb.closeConnect();
                return "";
            }
        }
      

        private void txtPhone_TextChanged(object sender, EventArgs e)
        {
            //txtFullName.Text = getCustomer("Fullname", "PhoneNumber", txtPhone.Text);
            lbName.Text = getCustomer("Fullname", "PhoneNumber", txtPhone.Text);
            txtIdentify.Text = getCustomer("IdentificationNumber", "PhoneNumber", txtPhone.Text);
            //txtAddress.Text = getCustomer("Address", "PhoneNumber", txtPhone.Text);
            lbAddress.Text = getCustomer("Address", "PhoneNumber", txtPhone.Text);
            //txtBirthday.Text = getCustomer("BirthDay", "PhoneNumber", txtPhone.Text);
            lbBirth.Text = getCustomer("BirthDay", "PhoneNumber", txtPhone.Text);
        }
        private void findLoad() {
            try
            {
                cnDb.connect();
                SqlCommand cmd = new SqlCommand("sp_ListRoomCheckOut", cnDb.cn);
                cmd.CommandText = "sp_ListRoomCheckOut";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@IdentificationNumber", txtIdentify.Text);
               // DataTable tb = new DataTable();
                SqlDataAdapter adt = new SqlDataAdapter(cmd);
                //adt.Fill(tb);
                DataSet ds = new DataSet();
                adt.Fill(ds);
                BindingSource bds = new BindingSource();
                bds.DataSource = ds.Tables[0];
                grvRoomBooking.DataSource = bds;
                cnDb.closeConnect();
            }
            catch (Exception)
            {
                cnDb.closeConnect();
                MessageBox.Show("CMND khong dung hoac co loi he thong");
            }
        }
        private void btFind_Click(object sender, EventArgs e)
        {
            if (txtIdentify.Text == "") {
                MessageBox.Show("Nhập CMND NGAY");
            }else{
                findLoad();
            }
            try {
                string totalCostAllRoom = "select sum(Price * (DATEDIFF(day,StartDay, getdate())+1))AS 'Cost all Room' "
                        + " from ListRoomBooking, BookingLog, Customer, Room, TypeRoom "
                        + " where BookingLog.IDBookingLog= ListRoomBooking.IDBookingLog "
                        + " and Customer.IdentificationNumber= BookingLog.IdentificationNumber "
                        + " and ListRoomBooking.IDRoom= Room.IDRoom "
                        + " and Room.IDTypeRoom =TypeRoom.IDTypeRoom "
                        + " and TypeBooking='Staying' "
                        + " and BookingLog.IdentificationNumber='"+txtIdentify.Text+"' "
                        + " and Cost = '0'";
                lbCostRoom.Text = cnDb.getOneData(totalCostAllRoom).ToString();
                        }
            catch (Exception) {
                cnDb.closeConnect();
            }
        }

        private void grvRoomBooking_CellLeave(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                string idRoom = grvRoomBooking.Rows[e.RowIndex].Cells[1].Value.ToString();
                string qr = "select Date,NameService, Price, Unit, Quantity, LogService.Cost  from Service "
                        + " join LogService on Service.IDService=LogService.IDService "
                        + " join ListRoomBooking on LogService.IDListRoom = ListRoomBooking.IDListRoom "
                        + " join BookingLog on ListRoomBooking.IDBookingLog= BookingLog.IDBookingLog "
                        + " and ListRoomBooking.Cost =0 and IDRoom='" + idRoom + "' and IdentificationNumber = '"+txtIdentify.Text+"'";
                BindingSource bdSDetailServive = cnDb.getData(qr);
                grvDetailService.DataSource = bdSDetailServive;

                string qrCostService = "select SUM(LogService.Cost)from Service "
                        + " join LogService on Service.IDService=LogService.IDService "
                        + " join ListRoomBooking on LogService.IDListRoom = ListRoomBooking.IDListRoom "
                        + " join BookingLog on ListRoomBooking.IDBookingLog= BookingLog.IDBookingLog "
                        + " and ListRoomBooking.Cost =0 and IDRoom='" + idRoom + "'  and IdentificationNumber = '" + txtIdentify.Text + "' ";
                lbCostService.Text = cnDb.getOneData(qrCostService).ToString();
            }
            catch (Exception) {
                cnDb.closeConnect();
            }
        }

      
        
        private void btPayment_Click(object sender, EventArgs e)
        {
            double totalCostPayment = 0;
            DataGridViewRow row = new DataGridViewRow();
            string mess = "TOTAL COST DETAIL \n----------------------------------------\n \n ";
            for (int i = 0; i < grvRoomBooking.Rows.Count; i++)
            {
                row = grvRoomBooking.Rows[i];
                if (Convert.ToBoolean(row.Cells[0].Value) == true)
                {//select checkbox
                    string idRoom = Convert.ToString(row.Cells[1].Value);
                    string nameRoom = Convert.ToString(row.Cells[2].Value);
                    string qrCostRoom = "select sum(Price * (DATEDIFF(day,StartDay, GETDATE())+1)) "
                        + " from ListRoomBooking, BookingLog, Customer, Room, TypeRoom "
                        + " where BookingLog.IDBookingLog= ListRoomBooking.IDBookingLog "
                        + " and Customer.IdentificationNumber= BookingLog.IdentificationNumber "
                        + " and ListRoomBooking.IDRoom= Room.IDRoom "
                        + " and Room.IDTypeRoom =TypeRoom.IDTypeRoom "
                        + " and BookingLog.IdentificationNumber='"+txtIdentify.Text+"' "
                        + " and Cost = '0' and ListRoomBooking.IDRoom= '"+idRoom+"' ";
                    string costRoom =cnDb.getOneData(qrCostRoom).ToString();
                    string qrCostService = "select SUM(LogService.Cost)from Service "
                        + " join LogService on Service.IDService=LogService.IDService "
                        + " join ListRoomBooking on LogService.IDListRoom = ListRoomBooking.IDListRoom "
                        + " join BookingLog on ListRoomBooking.IDBookingLog= BookingLog.IDBookingLog "
                        + " and ListRoomBooking.Cost =0 and IDRoom='" + idRoom + "'  and IdentificationNumber = '" + txtIdentify.Text + "' ";
                    string costService = cnDb.getOneData(qrCostService).ToString();
                    double doubleCostRoom, doubleCostService;
                    try
                    {
                        doubleCostRoom = Double.Parse(costRoom);
                    }
                    catch (Exception) {
                        doubleCostRoom = 0;
                    }
                    try
                    {
                        doubleCostService = Double.Parse(costService);
                    }
                    catch (Exception) {
                        doubleCostService = 0;
                    }                    
                    double totalCost = doubleCostRoom + doubleCostService;
                    mess = mess + "Room: " + nameRoom + " \n \t \t \t \t \t Room Cost: $" + costRoom + " \n \t \t \t \t \t Service Cost: $"
                                + costService + " \nTotal Cost of the Room " + nameRoom + " \t: $" + totalCost + " \n \n";
                    lbPayment.Text = mess;
                    lbPayment.Visible = true;
                    totalCostPayment = totalCostPayment + totalCost;

                    //update Room
                    string idBooking = Convert.ToString(row.Cells[6].Value);                    
                    string qrUpdate1 = "UPDATE ListRoomBooking SET [Cost] = '" + totalCost + "' WHERE IDListRoom = '"+idBooking+"'";
                    string qrUpdate2 = "UPDATE BookingLog SET TotalAmount = TotalAmount+'" + totalCostPayment 
                        + "' From ListRoomBooking WHERE BookingLog.IDBookingLog=ListRoomBooking.IDBookingLog and IDListRoom='" + idBooking + "'";
                    cnDb.xlExecuteNonQuery(qrUpdate1);
                    cnDb.xlExecuteNonQuery(qrUpdate2);
                    cnDb.xlExecuteNonQuery("UPDATE Room SET [Status] = 'Available' WHERE IDRoom='"+idRoom+"'");
                }
            }
            lbTotalPayment.Text = "---------------------------------------- \nTotal Cost Payment: $" + totalCostPayment;
            findLoad();
            cnDb.closeConnect();
            

        }

        private void btAcceptPayment_Click(object sender, EventArgs e)
        {
            DialogResult result = MessageBox.Show("Are you sure you want to pay for this bill?", "Payment", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (result == DialogResult.Yes)
                MessageBox.Show("Payment succcess. Total payment: $");
        }

    

      
      

    }
}
